﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Text;
using V5_Common;
using SubSonic.Query;

namespace V5_Data {
    public class V5SQLHelper {
        private static readonly V5SQLHelper m_Instance = new V5SQLHelper();
        /// <summary>
        /// 
        /// </summary>
        public static V5SQLHelper Instance {
            get { return m_Instance; }
        }
        private string _Pager;
        /// <summary>
        /// 分页数据
        /// </summary>
        public string Pager {
            get { return _Pager; }
            set { _Pager = value; }
        }
        /// <summary>
        /// 查询数据库记录
        /// </summary>
        public DataTable Query_By(string what, string from, string where, string orderby, int limit) {
            StringBuilder sbSQL = new StringBuilder();
            if (limit < 0) {
                limit = 10;
            }
            sbSQL.Append(" Select ");
            if (limit > 0) {
                sbSQL.Append(" Top " + limit + " ");
            }
            if (!string.IsNullOrEmpty(what)) {
                sbSQL.Append(" " + what + " ");
            }
            else {
                sbSQL.Append(" * ");
            }
            sbSQL.Append(" from " + from + " ");
            if (!string.IsNullOrEmpty(where)) {
                sbSQL.Append(" where " + where + " ");
            }
            if (!string.IsNullOrEmpty(orderby)) {
                sbSQL.Append(" order by " + orderby + " ");
            }
            var db = new V5_EShop.Data.BookEShopDB();
            SubSonic.Query.QueryCommand qcmd = new QueryCommand(sbSQL.ToString(), db.DataProvider);
            return db.DataProvider.ExecuteDataSet(qcmd).Tables[0];
        }
        /// <summary>
        /// 查询分页数据库记录
        /// </summary>
        public DataTable Query_Pager(string what, string from, string where, string orderby, int limit, int pageIndex, ref int recordCount) {
            var db = new V5_EShop.Data.BookEShopDB();
            if (pageIndex < 1) {
                pageIndex = 1;
            }
            StringBuilder strSql1 = new StringBuilder();
            strSql1.Append("select count(*) from " + from + " ");
            if (!string.IsNullOrEmpty(where)) {
                strSql1.Append(" where " + where + " ");
            }
            SubSonic.Query.QueryCommand qcmdCount = new QueryCommand(strSql1.ToString(), db.DataProvider);
            recordCount = db.DataProvider.ExecuteSingle<int>(qcmdCount);
            int startIndex = limit * (pageIndex - 1);
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (limit < 1) {
                limit = 10;
            }
            strSql.Append(" top " + limit + " ");
            if (string.IsNullOrEmpty(what)) {
                what = "*";
            }
            strSql.Append(" " + what + " from " + from + " where 1=1 ");
            if (!string.IsNullOrEmpty(where)) {
                strSql.Append(" and " + where + " ");
            }
            if (startIndex > 0) {
                strSql.Append(" and id not in(select top " + startIndex + " id from " + from + " where 1=1 ");
                if (!string.IsNullOrEmpty(where)) {
                    strSql.Append(" and " + where + " ");
                }
                if (!string.IsNullOrEmpty(orderby)) {
                    strSql.Append(" order by " + orderby + " ");
                }
                strSql.Append(" ) ");
            }
            if (!string.IsNullOrEmpty(orderby)) {
                strSql.Append(" order by " + orderby + " ");
            }
            SubSonic.Query.QueryCommand qcmd = new QueryCommand(strSql.ToString(), db.DataProvider);
            return db.DataProvider.ExecuteDataSet(qcmd).Tables[0];
        }


        public object ExecuteScalar(string SQL) {
            var db = new V5_EShop.Data.BookEShopDB();
            SubSonic.Query.QueryCommand qcmd = new QueryCommand(SQL, db.DataProvider);
            return db.DataProvider.ExecuteScalar(qcmd);
        }

        public int ExecuteNonQuery(string SQL) {
            var db = new V5_EShop.Data.BookEShopDB();
            SubSonic.Query.QueryCommand qcmd = new QueryCommand(SQL, db.DataProvider);
            return db.DataProvider.ExecuteQuery(qcmd);
        }
    }
}